/**
 * 
 */
package com.base.myproject.server;

import java.io.IOException;
import java.io.OutputStream;
import java.sql.ResultSet;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

import com.base.myproject.server.database.PoolMan;
import com.base.myproject.server.init.MyLogger;

/**
 * 负责下载excel的servlet
 * 
 * @author zgw 2009-11-12 上午03:51:36 javazgw@gmail.com
 */
public class ExcelServlet extends HttpServlet {
	
	
	protected void processRequest(HttpServletRequest request,
			HttpServletResponse response) throws ServletException, IOException {

		//response.setContentType("text/html");
		response.setCharacterEncoding("UTF-8");
		//PrintWriter out = response.getWriter();
		request.setCharacterEncoding("utf-8");

		response.setContentType("application/vnd.ms-excel");
//		response.addHeader("content-type", "application/x-msdownload;");
		response.addHeader("Content-Disposition",
				"attachment;   filename=\"huiyuan.xls" );

		String link =request.getParameter("link");
		MyLogger.getInstance().debug("excel "+link);
		if(DownContainer.getInstance().getLink(link)!=null)
		try {
			
			
			DownContainer.getInstance().remove(link);
			OutputStream os = response.getOutputStream();
			WritableWorkbook wwb = Workbook.createWorkbook(os);

		
			PoolMan pm = new PoolMan();
			String sql = "select *,b.finannce -isnull(c.paid,0) as lastfinance from ";
			sql +="( ";
			sql +="select ccode,cname,email,s_cardno,s_sex,s_edu,s_birid,s_mobil,s_idno,s_mark from ccode where ccode is not null ";
			sql +=") as a ";
			sql +="left join ";
			sql +="( ";
			sql +="select sum(isnull(finance*dc,0)) as finannce,ccode,max(updatedate) updatedate from s_service_finance group by ccode ";
			sql +=") as b ";

			sql +="on a.ccode = b.ccode ";
			sql +="left join ";
			sql +="( ";
			sql +="select sum(isnull(paid,0)) as paid,ccode from s_service_po where   busistatus=524288 group by ccode "; 
			sql +=") as c ";
			sql +="on a.ccode = c.ccode";

			ResultSet rs;
			try {
				rs = pm.executeQuery(sql);
	
				WritableSheet wsheet = wwb.createSheet("members_0", 0);
				Label label = new Label(0, 0, "会员号");

				wsheet.addCell(label);

				label = new Label(1, 0, "会员姓名");
				wsheet.addCell(label);
				label = new Label(2, 0, "会员卡号");
				wsheet.addCell(label);
				label = new Label(3, 0, "性别");
				wsheet.addCell(label);
				label = new Label(4, 0, "生日");
				wsheet.addCell(label);
				label = new Label(5, 0, "电话");
				wsheet.addCell(label);
				label = new Label(6, 0, "备注");
				wsheet.addCell(label);
				
//				columns.add(new ColumnConfig("finannce", "总充值金额", 100));
//				columns.add(new ColumnConfig("updatedate", "最后充值日期", 100));
//				columns.add(new ColumnConfig("paid", "总消费金额", 100));
//				columns.add(new ColumnConfig("lastfinance", "资金余额", 100));
//				
				label = new Label(7, 0, "总充值金额");
				wsheet.addCell(label);
				label = new Label(8, 0, "最后充值日期");
				wsheet.addCell(label);
				
				label = new Label(9, 0, "总消费金额");
				wsheet.addCell(label);
				label = new Label(10, 0, "资金余额");
				wsheet.addCell(label);
				
				int row = 1;
				while (rs.next()) {
					label = new Label(0,row, rs.getString("ccode"));
					wsheet.addCell(label);
					label = new Label( 1, row,rs.getString("cname"));
					wsheet.addCell(label);
					label = new Label( 2, row,rs.getString("s_cardno"));
					wsheet.addCell(label);

					label = new Label( 3, row,rs.getString("s_sex"));
					wsheet.addCell(label);

					label = new Label( 4, row,rs.getString("s_birid"));
					wsheet.addCell(label);

					label = new Label( 5, row,rs.getString("s_mobil"));
					wsheet.addCell(label);

					label = new Label( 6, row,rs.getString("s_mark"));
					wsheet.addCell(label);
					label = new Label( 7, row,rs.getString("finannce"));
					wsheet.addCell(label);
					label = new Label( 8, row,rs.getString("updatedate"));
					wsheet.addCell(label);
					label = new Label( 9, row,rs.getString("paid"));
					wsheet.addCell(label);
					label = new Label( 10, row,rs.getString("lastfinance"));
					wsheet.addCell(label);
					row++;
				}

			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}

			wwb.write();
			wwb.close();
			os.close();
			response.flushBuffer();
		} catch (RowsExceededException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (WriteException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		//return;
	}

	void writeAssociatorexcel() {

	}

	protected void doGet(HttpServletRequest request,
			HttpServletResponse response) throws ServletException, IOException {

		processRequest(request, response);
	}

	@Override
	protected void doPost(HttpServletRequest request,
			HttpServletResponse response) throws ServletException, IOException {

		processRequest(request, response);
	}
}
